if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MapStagedAgreementsFromInfoEd]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[MapStagedAgreementsFromInfoEd]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



/***************************************************************************
* Name:			MapStagedAgreementsFromInfoEd
* Purpose:		Update Type ID columns (both new and existing) to the Insight 
*				type IDs on the Staging table Stg_IE_AgrAGreement based on 
*				the data coming over from IE.
*
***************************************************************************/
CREATE PROCEDURE dbo.MapStagedAgreementsFromInfoEd
AS
BEGIN

	SET NOCOUNT ON

	-- Strip out any junk characters from the FolderNumber (
	-- the CompleteFolderNumber field holds the subprojects
	Update Stg_IE_AgrAGreement set FolderNumber = Replace (FolderNumber, '-', '')

	-- Mark the records that have multiple document numbers
	Update Stg_IE_AgrAGreement set HasMultipleDocs = 1 
	where 
		(select count(*) from Stg_IE_AgrAgreement s 
		 where s.foldernumber = Stg_IE_AgrAGreement.foldernumber) > 1 

	-- Populate current the PersonIds via the username.
	Update S 
	set PIPersonId = A.Id
	from Stg_IE_AgrAgreement S
	inner join FwkDomainUser U
		on U.UserName = S.PIUserName
	inner join AdmPerson A
		on A.FwkDomainUserId = U.Id

	-- FacultyId Match
	Update S set PIPersonId = Map.AdmPersonId
		From Stg_IE_AgrAgreement S
		inner join AdmPerson_InfoEdFaculty_Map Map
			On Map.Unique_Id = S.PIFacultyId
		Where S.PIPersonId is NULL
	
	-- Agreement Status Type
	Update S set AgrAgreementStatusTypeId = Sub.Id
		From Stg_IE_AgrAgreement S
		inner join AgrAgreementStatusType Sub
			on Sub.[Name] = S.OverallStatus
	
	-- AgrTransactionType
	Update S set AgrTransactionTypeId = Sub.Id
		FROM Stg_IE_AgrAgreement S
		inner join AgrTransactionType Sub
			on Sub.[Name] = S.TransactionType	

	-- Agreement Type.
	Update S set AgrAgreementTypeId = Sub.Id
		From Stg_IE_AgrAgreement S
		inner join AgrAgreementType Sub
			on Sub.[Name] = S.AgreementType

	-- AgrAgreementId
	Update s set AgrAgreementId = A.Id
	from AgrAgreement A
	inner join Stg_IE_AgrAgreement S on A.PropNumber = S.Prop_No


	-- MAP VALUES INSERTED FROM KEN'S PEOPLESOFT FEED THAT MIGHT ALREADY BE THERE
	-- BASED UPON THE FOLDER NUMBER (SINCE THOSE WILL BE CONSISTENT). 
--	Update S set AgrAgreementId = A.Id
--	From Stg_IE_AgrAgreement S
--	inner join AgrAgreement A on A.FolderNumber = S.FolderNumber 
--								and A.IsCurrent = s.IsCurrent and A.IsAmendment = 0
--		and a.documentNumber = (Select max(a1.documentnumber) from AgrAgreement a1
--								where a1.foldernumber =a.foldernumber
--									and a1.IsCurrent = a.IsCurrent
--									and a1.IsAmendment = a.IsAmendment)
--	WHERE S.AgrAgreementId is null and s.IsCurrent = 1
		

	-- Update existing values
	Update Stg_IE_AgrAgreement set	SvcEntityId = AgrAgreement.SvcEntityId,	
									FwkResourceId = AgrAgreement.FwkResourceId,
									AgrBudgetContainerId = AgrAgreement.AgrBudgetContainerId,
									AnnAnnotationContainerId = AgrAgreement.AnnAnnotationContainerId,
									ExistingPIPersonId = AgrAgreement.PIPersonId,
									ExistingProjectStartDate = AgrAgreement.ProjectStartDate,
									ExistingProjectEndDate = AgrAgreement.ProjectEndDate,
									ExistingReceivedDate = AgrAgreement.ReceivedDate,
									ExistingLegacyIdentifier = AgrAgreement.LegacyIdentifier,
									ExistinParentFolderNumber = AgrAgreement.ParentFolderNumber,
									ExistingAgreementTitle = CAST(AgrAgreement.Title as Varchar(4000)),
									ExistingAgreementShortTitle = AgrAgreement.ShortTitle,
									ExistingRFAID = AgrAgreement.RFAId,
									ExistingRFATitle = AgrAgreement.RFATitle,
									ExistingAgreementTypeId = AgrAgreement.AgrAgreementTypeId,
									ExistingAgrTransactionTypeId = AgrAgreement.AgrTransactionTypeId,
									ExistingAgrAgreementStatusTypeId = AgrAgreement.AgrAgreementStatusTypeId,
									ExistingDocumentNumber = AgrAgreement.DocumentNumber,
									ExistingProp_No	= AgrAgreement.PropNumber
		from AgrAgreement 
		where Stg_IE_AgrAgreement.AgrAgreementId = AgrAgreement.Id


	-- Existing PIPerson Usernames.
	Update Stg_IE_AgrAgreement set ExistingPIUserName = SubString(FwkDomainUser.UserName, 0, 10)
		From FwkDomainUser
			inner join AdmPerson Per
				on Per.FwkDomainUserId = FwkDomainUser.Id
			inner join AgrAgreement 
				on Per.Id = AgrAgreement.PIPersonId
		WHERE Stg_IE_AgrAgreement.AGrAGreementId = AgrAgreement.Id


	-- Existing Status
	Update Stg_IE_AgrAgreement set ExistingStatus = AgrAgreementStatusType.[Name]
		From AgrAgreementStatusType 
		inner join AgrAgreement 
			on AgrAgreement.AgrAgreementStatusTypeId = AgrAgreementStatusType.Id
		WHERE AgrAgreement.id = Stg_IE_AgrAgreement.AGrAGreementId

	-- Existing ACtivityType
	Update Stg_IE_AgrAgreement set ExistingActivityType = AgrActivityType.[Description]
		From AgrActivityType 
		inner join AgrActivity 
			on AgrActivity.AgrActivityTypeId = AgrActivityType.Id
		WHERE AgrActivity.AgrAgreementId = Stg_IE_AgrAgreement.AGrAGreementId

	-- Existing TransactionType
	Update Stg_IE_AgrAgreement set ExistingTransactionType = AgrTransactionType.[Name]
		From AgrTransactionType 
		inner join AgrAgreement 
			on AgrAgreement.AgrTransactionTypeId = AgrTransactionType.Id
		WHERE AgrAgreement.id = Stg_IE_AgrAgreement.AGrAGreementId

	-- Existing Sponsor		
	Update Stg_IE_AgrAgreement set ExistingAdmSponsorId = Map.AdmSponsorId,
								   ExistingSpon_Code = S.InfoEdSponCode
		From AgrAgreement Agr
		inner join SvcEntity_AdmSponsor_Map Map
			on Map.SvcEntityId = Agr.SvcEntityId
		inner join AdmSponsor S
			on Map.AdmSponsorId = S.Id
		Where Stg_IE_AgrAgreement.AgrAgreementId = Agr.Id
		and Map.AdmSponsorRelationTypeId = 1

	-- new Sponsor
	Update Stg_IE_AgrAgreement set AdmSponsorId = S.Id
		From AdmSponsor S
		where S.InfoEdSponCode = Stg_IE_AgrAgreement.Spon_Code


	-- AGRAgreementType Id (from IE)
	Update Stg_IE_AgrAgreement set AgrAgreementTypeId = AT.Id
		From AgrAgreementType AT
		where AT.[Name] = Stg_IE_AgrAgreement.NewAgreementType

	-- Existing Agreement Type (text)
	Update Stg_IE_AgrAgreement set ExistingAgreementType = AT.[Name]	
		From AgrAgreementType AT
			inner join AgrAgreement A
				on A.AgrAgreementTypeId = AT.Id
		where Stg_IE_AgrAgreement.AgrAgreementId = A.Id


	-- Set documnet number to 1 for new Agreements
	UPDATE Stg_IE_AgrAgreement SET DocumentNumber = 1
		WHERE AgrAgreementId IS NULL 

	-- Set Document Number to 1 for those records with only one entry in staging, 
	-- since we're bringing everything over.
	UPDATE Stg_IE_AgrAgreement SET DocumentNumber = 1
		WHERE isNull(HasMultipleDocs,0) = 0  

	-- Add calc here to update the DocumentNumber to the correct value based on 
	-- subprojects, setting the 'Master' record to the highest value
	Update Stg_IE_AgrAgreement set DocumentNumber = substring(CompleteFolderNumber, 13, 2) 
		where HasMultipleDocs = 1  and CompleteFolderNumber not like '%SubProj%' 
				and IsNumeric(substring(CompleteFolderNumber, 13, 2)) = 1

	-- Set document 1 (the Master doc) to be the Current one, since 
	-- that's how it will work with InfoEd.
	UPDATE Stg_IE_AgrAgreement SET IsCurrent = 1
		WHERE DocumentNumber = 1
	
	UPDATE Stg_IE_AgrAgreement SET IsCurrent = 0
		WHERE IsNull(DocumentNumber,0) != 1

	-- Ensure that multiple document numbered folders have 1 current document.
	Update Stg_IE_AgrAgreement set DocumentNumber = 1, IsCurrent = 1, IsAmendment = 0
	where HasMultipleDocs = 1 and (select count(*) from Stg_IE_AgrAgreement s 
		 where s.foldernumber = Stg_IE_AgrAGreement.foldernumber and s.IsCurrent = 1) = 0 
				and Stg_IE_AgrAgreement.documentnumber is null

	-- default this to 1, we won't be getting this from InfoEd
	UPDATE Stg_IE_AgrAgreement SET MultipleGrantNumbering = 1
		WHERE MultipleGrantNumbering IS NULL

	-- Set the Activity Type based on what's coming from InfoEd PS Code.
	UPDATE S set AgrActivityTypeId = Sub.Id
		From Stg_IE_AgrAgreement S
		inner join AgrActivityType Sub
			on Sub.PSCode = s.ActPSCode
		WHERE s.AgrActivityTypeId IS NULL

	-- Set the Activity Type based on InfoEd Activity Type String.
	UPDATE S set AgrActivityTypeId = Sub.Id
		From Stg_IE_AgrAgreement S
		inner join AgrActivityType Sub
			on Sub.[Description] = Substring(S.ActivityType, 0, CHARINDEX('(', S.ActivityType))
		WHERE s.AgrActivityTypeId IS NULL

	-- set the PS descriptions for the UI.
	UPDATE S set ActTypePSDescription = Sub.Description
		FROM Stg_IE_AgrAgreement S
		inner join ActivityTypePSCode Sub
			ON S.ActPSCode = Sub.PSCode

	SET NOCOUNT OFF

	END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO